package com.huixin.zhang.common.utils;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.text.DecimalFormat;
import java.util.*;

/**
 * POI相关操作
 * @author Toltech
 * @date Jan 14, 2019
 */
public class PoiUtils {
	private static Logger log = LoggerFactory.getLogger(PoiUtils.class);
	/**
	 * 生成Excel文件
	 * @param workbook
	 * @param fileName
	 * @return
	 */
	public static File createExcelFile(Workbook workbook, String fileName) {
		OutputStream stream = null;
		File file = null;
		try {
			file = File.createTempFile(fileName, ".xlsx");
			stream = new FileOutputStream(file.getAbsoluteFile());
			workbook.write(stream);
		} catch (FileNotFoundException e) {
			log.error("create excel file error.", e.getMessage());
		} catch (IOException e) {
			log.error("create excel io error.", e.getMessage());
		} finally {
			if(stream != null) {
				IOUtils.closeQuietly(stream);
			}
			if(workbook != null) {
				IOUtils.closeQuietly(workbook);
			}
		}
		return file;
	}

	/**
	 * 生成Excel文件
	 * @param workbook
	 * @param fileName
	 * @return
	 */
	public static File createExcelFile(HSSFWorkbook workbook, String fileName) {
		OutputStream stream = null;
		File file = null;
		try {
			file = File.createTempFile(fileName, ".xls");
			stream = new FileOutputStream(file.getAbsoluteFile());
			workbook.write(stream);
		} catch (FileNotFoundException e) {
			log.error("create excel file error.", e.getMessage());
		} catch (IOException e) {
			log.error("create excel io error.", e.getMessage());
		} finally {
			if(stream != null) {
				IOUtils.closeQuietly(stream);
			}
			if(workbook != null) {
				IOUtils.closeQuietly(workbook);
			}
		}
		return file;
	}
	
	/**
	 * 下载文件
	 * @param book
	 * @param fileName
	 */
	public static ResponseEntity<byte[]> downloadFile(HSSFWorkbook book, String fileName) {
		ByteArrayOutputStream out = null;
		try {
			HttpHeaders headers = new HttpHeaders();
			headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
			headers.add("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("ISO-8859-1"), "UTF-8"));
			out = new ByteArrayOutputStream();
			book.write(out);
			byte[] byteArray = out.toByteArray();
			
			ResponseEntity<byte[]> entity = new ResponseEntity<byte[]>(byteArray, headers, HttpStatus.OK);
			return entity;
			
		} catch (Exception e) {
			log.error("download excel error.", e.getMessage());
		} finally {
			if(out != null) {
				IOUtils.closeQuietly(out);
			}
			if(book != null) {
				IOUtils.closeQuietly(book);
			}
		}
		return new ResponseEntity<byte[]>(HttpStatus.NO_CONTENT);
	}

	public static List<Map<String,Object>> readExcel( Map<String,String> fieldNameMap, MultipartFile file) throws IOException {
		if (file == null || file.getSize() == 0){
			return null;
		}
		String fileName = file.getOriginalFilename();
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		if (fileName.endsWith(".xlsx") || fileName.endsWith(".xls")) {
			// 获取Excel文件对象
			Workbook wb = WorkbookFactory.create(file.getInputStream());
			// 获取文件指定工作表，默认第一个
			int sheet_size = wb.getNumberOfSheets();
			for (int index = 0; index < sheet_size; index++) {
				Sheet sheet = wb.getSheetAt(index);
				// 遍历行数(行记录，从0开始,首行记录数+1)
				Row title = sheet.getRow(0);
				if(title == null) {
					continue;
				}
				List<String> titlelist = new ArrayList<String>();
				for (int j = 0; j < title.getLastCellNum(); j++) {
					// 为每一个字段创建一个单元格对象
					Cell cell = title.getCell((short) (j));
					if (cell == null) {
						continue;
					}
					// 获取cell数据
					cell.setCellType(CellType.STRING);
					String data = getCellType(cell);
					titlelist.add(data);
				}
				w: for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
					// 创建一个行对象
					Row row = sheet.getRow(i);
					if (row == null || row.getLastCellNum() == -1) {
						break w;
					}
					// 遍历一行中的每个字段
					int max = sheet.getRow(0).getLastCellNum();
					Map<String,Object> obj = new HashMap<String,Object>();
					for (int j = 0; j < max; j++) {
						// 为每一个字段创建一个单元格对象
						Cell cell = row.getCell((short) (j));
						if (cell != null) {
							// 获取cell数据
							cell.setCellType(CellType.STRING);
							String data = getCellType(cell);
							if(!TextUtils.isBlank(data)){
								obj.put(fieldNameMap.get(titlelist.get(j)),list.get(j));
							}
						}
					}
					list.add(obj);
				}
			}
			wb.close();
		}
		return list;
	}

	/**
	 * EXCEL中的CELL值转换
	 *
	 * @param cell
	 * @return
	 */
	public static String getCellType(Cell cell) {
		String result = null;
		switch (cell.getCellType()) {
			case NUMERIC: // 数字类型，日期类型
				result = dealNum(cell);
				break;
			case STRING: // 字符串类型
				result = cell.getStringCellValue();
				result = dealStr(result);
				break;
			case FORMULA: // 公式
				result = cell.getCellFormula();
				break;
			default:
				result = "";
				break;
		}
		return result;
	}

	/**
	 * 处理数值类型
	 *
	 * @param cell
	 * @return
	 */
	public static String dealNum(Cell cell) {
		String str = "";
		if (HSSFDateUtil.isCellDateFormatted(cell)) {
			Date cellDate = cell.getDateCellValue();
			str = DateTimeUtils.formatAll(cellDate);
		} else {
			str = new DecimalFormat("0").format(cell.getNumericCellValue());
		}
		return str;
	}

	/**
	 * 处理字符串
	 *
	 * @param str
	 * @return
	 */
	public static String dealStr(String str) {
		if (str.trim().equals("") || str.trim().length() < 0) {
			str = "";
		}
		return str;
	}
}
